package com.etherpunk.droidsqlitescaffolding;

import java.awt.Container;
import java.awt.GridBagLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.beans.Introspector;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;


public class Scaffolding {

	private class ColumnStruct {
		public String ColumnName;
		public String ColumnType;
		public Boolean IsPrimaryKey;
	}

	private class TableStruct {
		public String TableName;
		ArrayList<ColumnStruct> ColumnList;
		public String PrimaryKeyColumnName;
	}
	public static void main(String[] args) {

		new Scaffolding();
	}
	private JTextField dbTextField = new JTextField(30);
	private JTextField outputTextField = new JTextField(30);
	private JTextField packageNameTextField = new JTextField(30);
	private JTextField databaseNameTextField = new JTextField(30);
	private String newLine = System.getProperty("line.separator");

	public Scaffolding(){
		JFrame guiFrame = new JFrame();

		//make sure the program exits when the frame closes
		guiFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		guiFrame.setTitle("Android / SQLite Scaffolding");
		guiFrame.setSize(150,150);

		guiFrame.setLocationRelativeTo(null);

		Container contentPane = guiFrame.getContentPane();
		GridBagLayout layout = new GridBagLayout();
		contentPane.setLayout(layout);

		// SQLite PANEL
		final JPanel databasePanel = new JPanel();
		databasePanel.setLayout(new GridLayout(4, 3));
		JLabel dbLocationLabel = new JLabel("SQLite DB:");
		JButton browseDbButton = new JButton( "...");
		databasePanel.add(dbLocationLabel);
		databasePanel.add(dbTextField);
		databasePanel.add(browseDbButton);


		// OUTPUT Panel
		JLabel outputLabel = new JLabel("Output Directory:");
		JButton browseOutputButton = new JButton("...");
		databasePanel.add(outputLabel);
		databasePanel.add(outputTextField);
		databasePanel.add(browseOutputButton);

		JLabel packageLabel = new JLabel("Package Name:");
		JLabel emptyLabel1 = new JLabel(" ");
		databasePanel.add(packageLabel);
		databasePanel.add(packageNameTextField);
		databasePanel.add(emptyLabel1);

		JLabel dbNameLabel = new JLabel("Database Name:");
		databasePanel.add(dbNameLabel);
		databasePanel.add(databaseNameTextField);


		JButton startButton = new JButton("Generate");
		databasePanel.add(startButton);

		contentPane.add(databasePanel);

		packageNameTextField.setText("com.test.myapp.database");
		databaseNameTextField.setText("mydb.db3");

		/* Testing values for another project of mine to make sure everything works correctly. */
		//dbTextField.setText("C:\\Users\\MannDesktop\\Desktop\\phc.db3");
		//outputTextField.setText("C:\\Users\\MannDesktop\\Dev\\workspace\\PersonalHealthChart\\src\\com\\etherpunk\\personalhealthchart\\database");
		//packageNameTextField.setText("com.etherpunk.personalhealthchart.database");
		//databaseNameTextField.setText("phc.db3");

		browseDbButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e){
				String filename = File.separator+"tmp";
				JFileChooser fc = new JFileChooser(new File(filename));

				// Show open dialog; this method does not return until the dialog is closed
				int returnValue = fc.showOpenDialog(null);

				if(returnValue != JFileChooser.APPROVE_OPTION) {
					return;
				}
				File selFile = fc.getSelectedFile();

				dbTextField.setText(selFile.getAbsolutePath());
			}
		});

		browseOutputButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e){
				String filename = File.separator+"tmp";
				JFileChooser fc = new JFileChooser(new File(filename));
				fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);

				// Show open dialog; this method does not return until the dialog is closed
				int returnValue = fc.showOpenDialog(null);

				if(returnValue != JFileChooser.APPROVE_OPTION) {
					return;
				}
				File selFile = fc.getSelectedFile();

				outputTextField.setText(selFile.getAbsolutePath());
			}
		});

		startButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				ArrayList<TableStruct> tableList = GenerateStructures();
				if(tableList == null) {
					return;
				}

				GenerateDbController(tableList);
				GenerateTableStructures(tableList);
				GenerateDataSources(tableList);

				JOptionPane.showMessageDialog(null, "Done.");

			}
		});		

		guiFrame.setContentPane(contentPane);
		guiFrame.pack();

		guiFrame.setVisible(true);
	}

	private void GenerateDataSources(ArrayList<TableStruct> tableList) {
		String content = "";
		try {
			for(int i=0; i<tableList.size(); i++) {
				File dataSourceOutput = new File(outputTextField.getText() + "\\" + tableList.get(i).TableName + "DataSource.java");

				String currentTableName = tableList.get(i).TableName;
				String lowerCaseCurrentTableName = Introspector.decapitalize(tableList.get(i).TableName);
				String tableInitial = lowerCaseCurrentTableName.substring(0,1);

				if (dataSourceOutput.exists()) {
					dataSourceOutput.delete();
				}
				dataSourceOutput.createNewFile();

				content = "package " + packageNameTextField.getText() + ";" + newLine + newLine +
						"import java.util.ArrayList;" + newLine +
						"" + newLine +
						"import android.content.Context;" + newLine +
						"import android.database.Cursor;" + newLine +
						"import android.database.SQLException;" + newLine +
						"import android.database.sqlite.SQLiteDatabase;" + newLine +
						"import android.util.Log;" + newLine + newLine +
						"public class "+ currentTableName + "DataSource {" + newLine + newLine +
						"\tprivate static final String LOGCAT = null;" + newLine + newLine +
						"\tprivate SQLiteDatabase database;" + newLine +
						"\tprivate DbController controller;" + newLine + newLine +
						
						// constructor
						"\tpublic " + currentTableName + "DataSource(Context context) {" + newLine +
						"\t\tcontroller = new DbController(context);" + newLine +
						"\t}" + newLine + newLine +
						"\tpublic void close() {" + newLine + 
						"\t\t controller.close();" + newLine +
						"\t}" + newLine + newLine +

						// delete 
						"\tpublic void delete" + currentTableName + "( String id ) {" + newLine +
						"\t\tLog.d(LOGCAT, \"Deleting " + currentTableName + ": \" + id); " + newLine +
						"\t\tthis.open();" + newLine +
						"\t\tString deleteQuery = \"DELETE FROM " + currentTableName + " WHERE " + tableList.get(i).PrimaryKeyColumnName + "'\" + id + \"'\";" + newLine +
						"\t\tLog.d(\"query\",\"Delete query:\" + deleteQuery);" + newLine +
						"\t\tdatabase.execSQL(deleteQuery);" + newLine + 
						"\t\tthis.close();" + newLine + 
						"\t}" + newLine + newLine + 
						
						// getAll
						"\tpublic ArrayList<" + currentTableName + "> getAll" + currentTableName + "() {" + newLine + 
						"\t\tArrayList<" + currentTableName + "> " + lowerCaseCurrentTableName + "List;" + newLine + 
						"\t\t" + lowerCaseCurrentTableName + "List = new ArrayList<" + currentTableName + ">();" + newLine + 
						"\t\tString selectQuery = \"SELECT * FROM " + currentTableName + ";\";" + newLine + 
						"\t\tthis.open();" + newLine + 
						"\t\tCursor cursor = database.rawQuery(selectQuery, null);" + newLine + 
						"\t\tif(cursor.moveToFirst()) {" + newLine + 
						"\t\t\tdo {" + newLine + 
						"\t\t\t\t" + currentTableName + " " + tableInitial + " = new " + currentTableName + "();" + newLine; // Start of the class

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += "\t\t\t\t" + tableInitial + "." + tableList.get(i).ColumnList.get(j).ColumnName + " = ";

					switch(tableList.get(i).ColumnList.get(j).ColumnType) {
					case "INTEGER":
						content += "Integer.parseInt(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					case "TEXT":
						content += "cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\"));";
						break;
					case "NUMERIC":
						content += "Float.parseFloat(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					case "REAL":
						content += "Double.parseDouble(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					}

					content += newLine;
				}

				content += newLine +
						"\t\t\t\t" + lowerCaseCurrentTableName + "List.add(" + tableInitial + ");" + newLine + 
						"\t\t\t} while(cursor.moveToNext());" + newLine +
						"\t\t}" + newLine + newLine +
						"\t\tthis.close();" + newLine +
						"\t\treturn " + lowerCaseCurrentTableName + "List;" + newLine +
						"\t}" + newLine + newLine + 
						
						// getSingle
						"\tpublic " + currentTableName + "  get" + currentTableName + "Info( String id ) {" + newLine + 
						"\t\t" + currentTableName + " " + lowerCaseCurrentTableName + " = new " + currentTableName + "();" + newLine +
						"\t\tString selectQuery = \"SELECT * FROM " + currentTableName + " WHERE " + tableList.get(i).PrimaryKeyColumnName + "='\" + id + \"'\";"+ newLine + 
						"\t\tthis.open();" + newLine + 
						"\t\tCursor cursor = database.rawQuery(selectQuery, null);" + newLine + 
						"\t\tif(cursor.moveToFirst()) {" + newLine + 
						"\t\t\tdo {" + newLine + 
						"\t\t\t\t" + currentTableName + " " + tableInitial + " = new " + currentTableName + "();" + newLine; // Start of the class
				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += "\t\t\t\t" + tableInitial + "." + tableList.get(i).ColumnList.get(j).ColumnName + " = ";

					switch(tableList.get(i).ColumnList.get(j).ColumnType) {
					case "INTEGER":
						content += "Integer.parseInt(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					case "TEXT":
						content += "cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\"));";
						break;
					case "NUMERIC":
						content += "Float.parseFloat(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					case "REAL":
						content += "Double.parseDouble(cursor.getString(cursor.getColumnIndex(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\")));";
						break;
					}

					content += newLine;
				}
				content += newLine +
						"\t\t\t} while(cursor.moveToNext());" + newLine +
						"\t\t}" + newLine + newLine +
						"\t\tthis.close();" + newLine +
						"\t\treturn " + lowerCaseCurrentTableName + ";" + newLine +
						"\t}" + newLine + newLine +

						// insert
						"\tpublic void insert" + currentTableName + "(" + currentTableName + " " + tableInitial + ") {" + newLine +
						"\t\tLog.d(LOGCAT, \"Creating new " + lowerCaseCurrentTableName + "\");" + newLine +
						"\t\tthis.open();" + newLine +
						"\t\tdatabase.insert(\"" + currentTableName + "\", null, " + tableInitial + ".GetContentValues());" + newLine +
						"\t\tthis.close();" + newLine +
						"\t}" + newLine + newLine +
						
						// open
						"\tpublic void open() throws SQLException {" + newLine +
						"\t\tdatabase = controller.getWritableDatabase();" + newLine +
						"\t}" + newLine + newLine + 
						
						// truncate
						"\tpublic void truncate" + currentTableName + "Table() {" + newLine +
						"\t\tLog.d(LOGCAT, \"Truncating " + currentTableName + " Table\");" + newLine +
						"\t\tthis.open();" + newLine +
						"\t\tString deleteQuery = \"DELETE FROM [" + currentTableName + "]\";" + newLine +
						"\t\tdatabase.execSQL(deleteQuery);" + newLine +
						"\t\tthis.close();" + newLine +
						"\t}" + newLine + newLine +
						
						// update
						"\tpublic int update" + currentTableName + "( " + currentTableName + " " + tableInitial + " ) {" + newLine +
						"\t\tLog.d(LOGCAT, \"Updating " + lowerCaseCurrentTableName + ": \" + " + tableInitial + "." + tableList.get(i).PrimaryKeyColumnName + ");" + newLine +
						"\t\tthis.open();" + newLine +
						"\t\tint returnValue = database.update(\"" + currentTableName + "\", " + tableInitial + ".GetContentValues(), \"" + tableList.get(i).PrimaryKeyColumnName + " = \" + " + tableInitial + "." + tableList.get(i).PrimaryKeyColumnName + ", null);" + newLine +
						"\t\tthis.close();" + newLine + 
						"\t\treturn returnValue;" + newLine +
						"\t}" + newLine +
						"}";

				FileWriter toFw = new FileWriter(dataSourceOutput.getAbsoluteFile());
				BufferedWriter toBw = new BufferedWriter(toFw);
				toBw.write(content);
				toBw.close();
			}
		} catch (Exception e) {
		}
	}

	private void GenerateDbController(ArrayList<TableStruct> tableList) {
		try {
			File outputDbController = new File(outputTextField.getText() + "\\DbController.java");

			if (outputDbController.exists()) {
				outputDbController.delete();
			}
			outputDbController.createNewFile();

			String content = "package " + packageNameTextField.getText() + ";" + newLine + newLine +
					"import android.content.Context;" + newLine + 
					"import android.database.sqlite.SQLiteDatabase;" + newLine + 
					"import android.database.sqlite.SQLiteOpenHelper;" + newLine + 
					"import android.util.Log;" + newLine + 
					"" + newLine + 
					"" + newLine + 
					"public class DbController extends SQLiteOpenHelper {" + newLine + 
					"" + newLine + 
					"\tprivate static final String LOGCAT = null;" + newLine +  
					"" + newLine + 

					// constructor
					"\tpublic DbController(Context applicationcontext) {" + newLine + 
					"\t\tsuper(applicationcontext, \"" + databaseNameTextField.getText() + "\", null, 1);" + newLine + 
					"\t\tLog.d(LOGCAT, \"Database created.\");" + newLine +
					"\t}" + newLine +
					"" + newLine + 
					"" + newLine + 
					"\t@Override" + newLine +
					
					// onCreate
					"\tpublic void onCreate(SQLiteDatabase database) {" + newLine +
					"\t\tString query;" + newLine +
					"" + newLine;

			for(int i=0; i<tableList.size(); i++) {

				content += "\t\t// " + tableList.get(i).TableName + " table" + newLine +
						"\t\tquery = \"CREATE TABLE [" + tableList.get(i).TableName + "] ( ";

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += tableList.get(i).ColumnList.get(j).ColumnName + " " + tableList.get(i).ColumnList.get(j).ColumnType;
					if(tableList.get(i).ColumnList.get(j).IsPrimaryKey == true) {
						content += " PRIMARY KEY";
					}
					if (j != tableList.get(i).ColumnList.size()-1) {
						content += ", ";
					} else {
						content += ");\";" + newLine;
					}

				}
				content += "\t\tdatabase.execSQL(query);" + newLine +
						"\t\tLog.d(LOGCAT, \"[" + tableList.get(i).TableName + "] table created.\");" + newLine + newLine;				
			}

			content += "\t}" + newLine + newLine +
					"\t@Override" + newLine +
					
					// onUpgrade
					"\tpublic void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {" + newLine + 
					"\t\tLog.d(LOGCAT, \"Oldv:\" + version_old + \" | Curr:\" + current_version);" + newLine +
					"\t}" + newLine +
					"}";

			FileWriter fw = new FileWriter(outputDbController.getAbsoluteFile());
			BufferedWriter bw = new BufferedWriter(fw);
			bw.write(content);
			bw.close();

		} catch (Exception e) {

		}
	}

	private ArrayList<TableStruct> GenerateStructures() {

		String newLine = System.getProperty("line.separator");
		String errorMessage = "";
		Boolean isReady = true;
		if(dbTextField.getText() == "") {
			errorMessage += "No database file selected!" + newLine;
			isReady = false;
		}

		if(new File(dbTextField.getText()).exists() == false) {
			errorMessage += "Sqlite DB File does not exists!" + newLine;
			isReady = false;
		}

		if(new File(outputTextField.getText()).exists() == false) {
			errorMessage += "Output directory does not exists!" + newLine;
			isReady = false;
		}

		if(packageNameTextField.getText() == "") {
			errorMessage += "A package name is required!" + newLine;
			isReady = false;
		}

		if(databaseNameTextField.getText() == "") {
			errorMessage += "A database filename is required for your app!" + newLine;
			isReady = false;
		}

		if ( isReady == false) {
			JOptionPane.showMessageDialog(null, errorMessage);
			return null;
		}

		ArrayList<TableStruct> tableList = new ArrayList<TableStruct>();

		Connection c= null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:" + dbTextField.getText());
			Statement stmt = null;
			Statement stmt2 = null;
			stmt = c.createStatement();
			stmt2 = c.createStatement();
			ResultSet rs = stmt.executeQuery( "SELECT * FROM sqlite_master WHERE type='table';" );


			while(rs.next()) {


				TableStruct ts = new TableStruct();
				ts.ColumnList = new ArrayList<ColumnStruct>();
				ts.TableName = rs.getString(2);				

				ResultSet rs2 = stmt2.executeQuery( "pragma table_info(" + ts.TableName + ");" );
				while(rs2.next()) {
					ColumnStruct cs = new ColumnStruct();

					cs.ColumnName = rs2.getString(2);
					cs.ColumnType = rs2.getString(3);
					if(rs2.getInt(6) == 1) {
						cs.IsPrimaryKey = true;
						ts.PrimaryKeyColumnName = cs.ColumnName;
					} else {
						cs.IsPrimaryKey = false;
					}

					ts.ColumnList.add(cs);
				}

				tableList.add(ts);
			}

		}catch (Exception e) {
			JOptionPane.showMessageDialog(null, "ERROR: " + e.getMessage());
		}

		return tableList;
	}

	private void GenerateTableStructures(ArrayList<TableStruct> tableList) {
		String content = "";
		try {
			for(int i=0; i<tableList.size(); i++) {
				File tableOutput = new File(outputTextField.getText() + "\\" + tableList.get(i).TableName + ".java");

				if (tableOutput.exists()) {
					tableOutput.delete();
				}
				tableOutput.createNewFile();

				content = "package " + packageNameTextField.getText() + ";" + newLine + newLine +
						"import android.content.ContentValues;" + newLine + newLine +
						"public class " + tableList.get(i).TableName + " {" + newLine +
						"" + newLine;

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += "\tpublic " + GetDataType(tableList.get(i).ColumnList.get(j).ColumnType) + " " + tableList.get(i).ColumnList.get(j).ColumnName + ";" + newLine;
				}

				content += newLine +
						"\tpublic " + tableList.get(i).TableName + "() {" + newLine + newLine + 
						"\t}" + newLine + newLine;

				content += "\tpublic " + tableList.get(i).TableName + "( ";

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += GetDataType(tableList.get(i).ColumnList.get(j).ColumnType) + " " + Introspector.decapitalize(tableList.get(i).ColumnList.get(j).ColumnName);

					if (j != tableList.get(i).ColumnList.size()-1) {
						content += ", ";
					}
				}

				content += " ) {" + newLine;

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += "\t\tthis." + tableList.get(i).ColumnList.get(j).ColumnName + " = " + Introspector.decapitalize(tableList.get(i).ColumnList.get(j).ColumnName) + ";" + newLine;
				}

				content += "\t}" + newLine + newLine +
						"\tpublic ContentValues GetContentValues() {" + newLine +
						"\t\tContentValues values = new ContentValues();" + newLine;

				for(int j=0;j<tableList.get(i).ColumnList.size();j++){
					content += "\t\tvalues.put(\"" + tableList.get(i).ColumnList.get(j).ColumnName + "\", this." + tableList.get(i).ColumnList.get(j).ColumnName + ");" + newLine;
				}
				content += "\t\treturn values;" + newLine +
						"\t}" + newLine + "}";


				FileWriter toFw = new FileWriter(tableOutput.getAbsoluteFile());
				BufferedWriter toBw = new BufferedWriter(toFw);
				toBw.write(content);
				toBw.close();

			}
		}catch (Exception e) {

		}
	}

	private String GetDataType(String SqliteType) {
		/* May need to custom map these in the gui */
		switch(SqliteType) {
		case "INTEGER":
			return "int";
		case "TEXT":
			return "String";
		case "NUMERIC":
			return "float";
		case "REAL":
			return "double";
		default:
			return "ERROR";
		}
	}



}
